﻿using System;
using System.Data;
using System.Data.SqlClient;
using AccessControl.Common;

namespace AccessControl.DataAcess
{
    public class EventAccess
    {
        public static AccessControlDS.tblEventDataTable SelectAll(AccessControlDS.tblEventDataTable tbl)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdSelect = new SqlCommand("SELECT * FROM tblEvent", cn);
                try
                {
                    cn.Open();
                    var reader = cmdSelect.ExecuteReader();
                    while (reader.Read())
                    {
                        var newRow = tbl.NewtblEventRow();
                        newRow.pkEvent = (int)reader["pkEvent"];
                        newRow.EventName = (String)reader["EventName"];
                        newRow.fkEventType = (int)reader["fkEventType"];
                        if (reader["fkTwinEvent"] != DBNull.Value)
                            newRow.fkTwinEvent = (int)reader["fkTwinEvent"];
                        tbl.AddtblEventRow(newRow);
                    }
                }
                finally
                {
                    cn.Close();
                }
                return tbl.Rows.Count != 0 ? tbl : null;
            }
        }



        public static AccessControlDS.tblEventDataTable SelectByFilter(string filter)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdSelect = new SqlCommand(String.Format("SELECT * FROM tblEvent WHERE {0}", filter), cn);
                var tblEventTable = new AccessControlDS.tblEventDataTable();
                try
                {
                    cn.Open();
                    var reader = cmdSelect.ExecuteReader();
                    while (reader.Read())
                    {
                        var newRow = tblEventTable.NewtblEventRow();
                        newRow.pkEvent = (int)reader["pkEvent"];
                        newRow.EventName = (String)reader["EventName"];
                        newRow.fkEventType = (int)reader["fkEventType"];
                        if (reader["fkTwinEvent"]!=DBNull.Value)
                            newRow.fkTwinEvent = (int)reader["fkTwinEvent"];
                        tblEventTable.AddtblEventRow(newRow);
                    }
                }
                finally
                {
                    cn.Close();
                }
                return tblEventTable.Rows.Count != 0 ? tblEventTable : null;
            }
        }

        public static void Insert(AccessControlDS.tblEventRow row)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdInsert = new SqlCommand("spEvent_Insert", cn) { CommandType = CommandType.StoredProcedure };
                cmdInsert.Parameters.AddWithValue("@EventName", row.EventName);
                cmdInsert.Parameters.AddWithValue("@fkEventType", row.fkEventType);
                cmdInsert.Parameters.AddWithValue("@fkTwinEvent", row.fkTwinEvent);
                try
                {
                    cn.Open();
                    cmdInsert.ExecuteNonQuery();
                }
                finally
                {
                    cn.Close();
                }
            }
        }

        public static void Update(AccessControlDS.tblEventRow row)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdUpdate = new SqlCommand("spEvent_Update", cn) { CommandType = CommandType.StoredProcedure };
                cmdUpdate.Parameters.AddWithValue("@pkEvent", row.pkEvent);
                cmdUpdate.Parameters.AddWithValue("@EventName", row.EventName);
                cmdUpdate.Parameters.AddWithValue("@fkEventType", row.fkEventType);
                cmdUpdate.Parameters.AddWithValue("@fkTwinEvent", row.fkTwinEvent);
                try
                {
                    cn.Open();
                    cmdUpdate.ExecuteNonQuery();
                }
                finally
                {
                    cn.Close();
                }
            }
        }

        public static void Delete(AccessControlDS.tblEventRow row)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdDelete = new SqlCommand("spEvent_Delete", cn) { CommandType = CommandType.StoredProcedure };
                cmdDelete.Parameters.AddWithValue("@pkEvent", row.pkEvent);
                try
                {
                    cn.Open();
                    cmdDelete.ExecuteNonQuery();
                }
                finally
                {
                    cn.Close();
                }
            }
        }

    }
}
